package com.warm.pump.module.work.controller;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.warm.pump.frame.db.DbUtil;
import com.warm.pump.module.work.bean.WorkCopyTable;
import org.apache.commons.collections.MapUtils;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.stereotype.Controller;

import com.warm.pump.frame.gen.bean.PageBean;
import com.warm.pump.module.work.bean.po.gen.WorkDb;
import com.warm.pump.module.work.service.IWorkDbService;
import com.warm.pump.frame.gen.util.OutJson;
import com.warm.pump.frame.gen.util.MyBeanUtils;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;

/**
 *  work_db
 *
 */
@Controller
@RequestMapping("/workDb")
public class WorkDbController {
	@Autowired
	private IWorkDbService workDbService;
	
	/**
	 * 列表
	 */
	@ResponseBody
	@RequestMapping("/list")
	@RequiresPermissions("workDb:list")
	public OutJson list(Integer page, Integer limit,WorkDb workDb){
		Map<String, Object> map = new HashMap<>();
		MyBeanUtils.copyBean2Map(map,workDb);

		if(StringUtils.isNotEmpty(workDb.getSidx())&&StringUtils.isNotEmpty(workDb.getOrder())){
			map.put("orderBy",workDb.getSidx()+" "+workDb.getOrder());
		}
       //查询列表数据
        PageBean pageBean = workDbService.getPageBeanByMap(map, page, limit);

		return OutJson.ok().put("page", pageBean);
	}


	/**
	 * 信息
	 */
	@ResponseBody
	@RequestMapping("/info/{id}")
	@RequiresPermissions("workDb:info")
	public OutJson info(@PathVariable("id") Long  id){
		WorkDb workDb = workDbService.queryObject(id);
		
		return OutJson.ok().put("workDb", workDb);
	}


	/**
	 * 保存
	 */
	@ResponseBody
	@RequestMapping("/save")
	@RequiresPermissions("workDb:save")
	public OutJson save(@RequestBody WorkDb workDb){
		workDbService.save(workDb);
		
		return OutJson.ok();
	}
	
	/**
	 * 修改
	 */
	@ResponseBody
	@RequestMapping("/update")
	@RequiresPermissions("workDb:update")
	public OutJson update(@RequestBody WorkDb workDb){
		workDbService.update(workDb);
		
		return OutJson.ok();
	}
	
	/**
	 * 删除
	 */
	@ResponseBody
	@RequestMapping("/delete")
	@RequiresPermissions("workDb:delete")
	public OutJson delete(@RequestBody Long [] ids){
		workDbService.deleteBatch(ids);
		
		return OutJson.ok();
	}


	@ResponseBody
	@RequestMapping("/testConnection")
	public OutJson testConnection(String url, String pass, String user) {
		boolean flag = workDbService.testConnection(url, user, pass);
		return OutJson.ok().setResData(flag ? 1 : 0);
	}

	/**
	 * 通过dbkey获取表名列表
	 *
	 * @param dbKey
	 * @param qryName
	 * @return
	 */
	@ResponseBody
	@RequestMapping("/qryTablesByDbKey/{dbKey}")
	public List<Map<String, Object>> qryTablesByDbKey(@PathVariable("dbKey") String dbKey, String qryName) {

		List<Map<String, Object>> tablesFromDb = DbUtil.getTablesFromDb(dbKey, qryName);
		List<Map<String, Object>> rtList = new ArrayList<>();
		for (Map<String, Object> tables : tablesFromDb) {
			String tableName = MapUtils.getString(tables, "table_name");
			String tableComment = MapUtils.getString(tables, "table_comment");

			Map<String, Object> rtMap = new HashMap<>();
			rtMap.put("id", tableName);
			rtMap.put("value", tableName);
			rtMap.put("label", tableComment);
			rtList.add(rtMap);
		}
		return rtList;
	}

	@ResponseBody
	@RequestMapping("/qrySelectSqlByTableName")
	public OutJson qrySelectSqlByTableName(String dbKey, String qryName) {

		String selectSqlByTableName = DbUtil.getSelectSqlByTableName(dbKey, qryName);
		return OutJson.ok().put("selectSql", selectSqlByTableName);
	}


	@ResponseBody
	@RequestMapping("/qryInsertSqlByTableName")
	public OutJson qryInsertSqlByTableName(String dbKey, String qryName) {

		String insertSqlByTableName = DbUtil.getInsertSqlByTableName(dbKey, qryName);
		return OutJson.ok().put("insertSql", insertSqlByTableName);
	}

	/**
	 * 复制表
	 *
	 * @param workCopyTable
	 * @return
	 */
	@ResponseBody
	@RequestMapping("/copyTable")
	public OutJson copyTable(@RequestBody WorkCopyTable workCopyTable) {

		//获取from数据源
		DataSource dataSourceFrom = DbUtil.getDataSource(workCopyTable.getDbKeyFrom());
		//获取to数据源
		DataSource dataSourceTo = DbUtil.getDataSource(workCopyTable.getDbKeyTo());
		Connection conn = null;
		try {
			Platform platform = PlatformFactory.createNewPlatformInstance(dataSourceFrom);
			conn = platform.getDataSource().getConnection();
			String catalog = conn.getCatalog();
			Database db = platform.readModelFromDatabase(catalog);
			Table tableFrom = db.findTable(workCopyTable.getTableFrom());
			tableFrom.setName(workCopyTable.getTableTo());

			Platform platformTo = PlatformFactory.createNewPlatformInstance(dataSourceTo);
			Database db2 = new Database();
			db2.addTable(tableFrom);
			platformTo.createTables(db2, false, false);
		} catch (Exception e) {
			e.printStackTrace();
			return OutJson.error("复制失败，详情请看后台日志。");
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
				return OutJson.error("复制失败，详情请看后台日志。");
			}
		}
		return OutJson.ok();
	}
	
}
